<?php
namespace app\controller;

use app\BaseController;
use think\facade\Db;
use think\Request;
use think\facade\View;
use think\exception\ValidateException;

// 推荐课程数据
class CourseData extends BaseController
{


    public function index()
    {
        $function = Db::name('members')->group('function')->column('function');

        View::assign('function', $function);
        return View::fetch();
    }


    public function load(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return json(['code' => 500, 'data' => '', 'msg' => '无操作权限']);
        }
        $get = $request->get();

        $spark = [];
        $qxt = [];

        if (empty($get['function']) && empty($get['age'])) {
            $age_course = Db::name('age_course')->select()->toArray();
            foreach ($age_course as &$v) {
                if ($v['type'] == 'spark') {
                    array_push($spark, $v['courseid']);
                } else if ($v['type'] == 'qxt') {
                    array_push($qxt, $v['courseid']);
                }
            }

            $job_function_course = Db::name('job_function_course')->select()->toArray();
            foreach ($job_function_course as &$val) {
                if ($v['type'] == 'spark') {
                    array_push($spark, $val['courseid']);
                } else if ($v['type'] == 'qxt') {
                    array_push($qxt, $val['courseid']);
                }
            }
        } else {

            if (!empty($get['function']) && !empty($get['age'])) {
                $age_where = [];
                if (!empty($get['age'])) {
                    $age_where[] = ['age_id', '=', $get['age']];
                }

                $age_course = Db::name('age_course')->where($age_where)->select()->toArray();
                if (!empty($age_course)) {
                    foreach ($age_course as &$v) {
                        if ($v['type'] == 'spark') {
                            array_push($spark, $v['courseid']);
                        } else if ($v['type'] == 'qxt') {
                            array_push($qxt, $v['courseid']);
                        }
                    }
                }


                $function_where = [];
                if (!empty($get['function'])) {
                    $function_where[] = ['function', '=', $get['function']];
                }

                $job_function_course = Db::name('job_function_course')->where($function_where)->select()->toArray();
                if (!empty($job_function_course)) {
                    foreach ($job_function_course as &$val) {
                        if ($val['type'] == 'spark') {
                            array_push($spark, $val['courseid']);
                        } else if ($val['type'] == 'qxt') {
                            array_push($qxt, $val['courseid']);
                        }
                    }
                }

            } else if(!empty($get['function']) && empty($get['age'])) {
                $function_where = [];
                if (!empty($get['function'])) {
                    $function_where[] = ['function', '=', $get['function']];
                }

                $job_function_course = Db::name('job_function_course')->where($function_where)->select()->toArray();
                if (!empty($job_function_course)) {
                    foreach ($job_function_course as &$val) {
                        if ($val['type'] == 'spark') {
                            array_push($spark, $val['courseid']);
                        } else if ($val['type'] == 'qxt') {
                            array_push($qxt, $val['courseid']);
                        }
                    }
                }
            } elseif (empty($get['function']) && !empty($get['age'])) {
                $age_where = [];
                if (!empty($get['age'])) {
                    $age_where[] = ['age_id', '=', $get['age']];
                }

                $age_course = Db::name('age_course')->where($age_where)->select()->toArray();
                if (!empty($age_course)) {
                    foreach ($age_course as &$v) {
                        if ($v['type'] == 'spark') {
                            array_push($spark, $v['courseid']);
                        } else if ($v['type'] == 'qxt') {
                            array_push($qxt, $v['courseid']);
                        }
                    }
                }
            }


        }

        if (empty($get['course_type'])) {
            //+spark数据
            $spark_course = array_unique($spark);
            $spark_where = [];
            $spark_where[] = ['id', 'in', $spark_course];
            if (!empty($get['key'])) {
                $spark_where[] = ['title', 'like', '%'.$get['key'].'%'];
            }
            $spark_data = Db::name('learning')
                ->where($spark_where)
                ->field('id, title')
                ->select()
                ->toArray();

            foreach ($spark_data as &$v) {
                //+指定时间内的点击量及曝光量
                $recommend_where = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where[] = ['type', '=', 1];
                $recommend_where[] = ['courseid', '=', $v['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $v['exposure'] = 0;
                } else {
                    $v['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $v['click'] = 0;
                } else {
                    $v['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $v['ctr'] = 0;
                } else {
                    $v['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $v['course_type'] = 'Spark';
            }

            //+轻学堂数据
            $qxt_course = array_unique($qxt);
            $qxt_where = [];
            $qxt_where[] = ['id', 'in', $qxt_course];
            if (!empty($get['key'])) {
                $qxt_where[] = ['courseTitle', 'like', '%'.$get['key'].'%'];
            }
            $qxt_data = Db::name('qxt_courses')
                ->where($qxt_where)
                ->field('id, courseTitle as title')
                ->select()->toArray();

            foreach ($qxt_data as &$value) {
                //+指定时间内的点击量及曝光量
                $recommend_where2 = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where2[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where2[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where2[] = ['type', '=', 3];
                $recommend_where2[] = ['courseid', '=', $value['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where2)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $value['exposure'] = 0;
                } else {
                    $value['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $value['click'] = 0;
                } else {
                    $value['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $value['ctr'] = 0;
                } else {
                    $value['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $value['course_type'] = '轻学堂';
            }


            $data = array_merge($spark_data, $qxt_data);

        } else if($get['course_type'] == 1) {
            $spark_course = array_unique($spark);
            $spark_where = [];
            $spark_where[] = ['id', 'in', $spark_course];

            if (!empty($get['key'])) {
                $spark_where[] = ['title', 'like', '%'.$get['key'].'%'];
            }
            $data = Db::name('learning')
                ->where($spark_where)
                ->field('id, title')
                ->select()
                ->toArray();

            foreach ($data as &$v) {
                //+指定时间内的点击量及曝光量
                $recommend_where = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where[] = ['type', '=', 1];
                $recommend_where[] = ['courseid', '=', $v['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $v['exposure'] = 0;
                } else {
                    $v['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $v['click'] = 0;
                } else {
                    $v['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $v['ctr'] = 0;
                } else {
                    $v['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $v['course_type'] = 'Spark';
            }

        } else if($get['course_type'] == 3) {
            $qxt_course = array_unique($qxt);
            $qxt_where = [];
            $qxt_where[] = ['id', 'in', $qxt_course];
            if (!empty($get['key'])) {
                $qxt_where[] = ['courseTitle', 'like', '%'.$get['key'].'%'];
            }
            $data = Db::name('qxt_courses')
                ->where($qxt_where)
                ->field('id, courseTitle as title')
                ->select()
                ->toArray();

            foreach ($data as &$value) {
                //+指定时间内的点击量及曝光量
                $recommend_where2 = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where2[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where2[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where2[] = ['type', '=', 3];
                $recommend_where2[] = ['courseid', '=', $value['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where2)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $value['exposure'] = 0;
                } else {
                    $value['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $value['click'] = 0;
                } else {
                    $value['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $value['ctr'] = 0;
                } else {
                    $value['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $value['course_type'] = '轻学堂';
            }

        }

        if (!empty($data)) {
            if ( !empty($get['field']) && !empty($get['order'])) {
                $field = $get['field'];
                $sort = $get['order'];
                $data = sort_array($data, $field, $sort);
            }

            //+分页
            $limit = $get['limit'];
            $pages = $get['page'];
            if(empty($pages) || $pages == 1){
                $Pages = 0;
            }else{
                $Pages = ($pages-1)*$limit;
            }

            $datas = array_slice($data,$Pages,$limit);

            $data1 = array(
                'code'=> 0,
                'msg'=> '',
                'count' => count($data),
                'data'=> $datas
            );
        } else {
            $data1 = array(
                'code'=> 0,
                'msg'=> '',
                'count' => 0,
                'data'=> []
            );
        }
        return json($data1);
    }

    // 数据导出
    public function export(Request $request)
    {
        if(!check_jurisdiction(session('admin_auth.role_id'), $this->control, $this->action)) {
            return $this->outputAjax('无操作权限');
        }
        set_time_limit(0);
        //设置程序运行内存
        ini_set('memory_limit', '4096M');

        $fileName = '推荐课程数据'.date('Ymd', time());
        header('Content-Encoding: UTF-8');
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');


        //打开php标准输出流
        $fp = fopen('php://output', 'a');

        //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));


        //添加导出标题
        fputcsv($fp, ['CourseId', '课程标题', '课程类型', 'CTR', '推荐曝光量', '推荐点击量']);

        $get = $request->get();

        $spark = [];
        $qxt = [];

        if (empty($get['function']) && empty($get['age'])) {
            $age_course = Db::name('age_course')->select()->toArray();
            foreach ($age_course as &$v) {
                if ($v['type'] == 'spark') {
                    array_push($spark, $v['courseid']);
                } else if ($v['type'] == 'qxt') {
                    array_push($qxt, $v['courseid']);
                }
            }

            $job_function_course = Db::name('job_function_course')->select()->toArray();
            foreach ($job_function_course as &$val) {
                if ($v['type'] == 'spark') {
                    array_push($spark, $val['courseid']);
                } else if ($v['type'] == 'qxt') {
                    array_push($qxt, $val['courseid']);
                }
            }
        } else {

            if (!empty($get['function']) && !empty($get['age'])) {
                $age_where = [];
                if (!empty($get['age'])) {
                    $age_where[] = ['age_id', '=', $get['age']];
                }

                $age_course = Db::name('age_course')->where($age_where)->select()->toArray();
                if (!empty($age_course)) {
                    foreach ($age_course as &$v) {
                        if ($v['type'] == 'spark') {
                            array_push($spark, $v['courseid']);
                        } else if ($v['type'] == 'qxt') {
                            array_push($qxt, $v['courseid']);
                        }
                    }
                }


                $function_where = [];
                if (!empty($get['function'])) {
                    $function_where[] = ['function', '=', $get['function']];
                }

                $job_function_course = Db::name('job_function_course')->where($function_where)->select()->toArray();
                if (!empty($job_function_course)) {
                    foreach ($job_function_course as &$val) {
                        if ($val['type'] == 'spark') {
                            array_push($spark, $val['courseid']);
                        } else if ($val['type'] == 'qxt') {
                            array_push($qxt, $val['courseid']);
                        }
                    }
                }

            } else if(!empty($get['function']) && empty($get['age'])) {
                $function_where = [];
                if (!empty($get['function'])) {
                    $function_where[] = ['function', '=', $get['function']];
                }

                $job_function_course = Db::name('job_function_course')->where($function_where)->select()->toArray();
                if (!empty($job_function_course)) {
                    foreach ($job_function_course as &$val) {
                        if ($val['type'] == 'spark') {
                            array_push($spark, $val['courseid']);
                        } else if ($val['type'] == 'qxt') {
                            array_push($qxt, $val['courseid']);
                        }
                    }
                }
            } elseif (empty($get['function']) && !empty($get['age'])) {
                $age_where = [];
                if (!empty($get['age'])) {
                    $age_where[] = ['age_id', '=', $get['age']];
                }

                $age_course = Db::name('age_course')->where($age_where)->select()->toArray();
                if (!empty($age_course)) {
                    foreach ($age_course as &$v) {
                        if ($v['type'] == 'spark') {
                            array_push($spark, $v['courseid']);
                        } else if ($v['type'] == 'qxt') {
                            array_push($qxt, $v['courseid']);
                        }
                    }
                }
            }
        }

        if (empty($get['course_type'])) {
            //+spark数据
            $spark_course = array_unique($spark);
            $spark_where = [];
            $spark_where[] = ['id', 'in', $spark_course];
            if (!empty($get['key'])) {
                $spark_where[] = ['title', 'like', '%'.$get['key'].'%'];
            }
            $spark_data = Db::name('learning')
                ->where($spark_where)
                ->field('id, title')
                ->select()->toArray();

            foreach ($spark_data as &$v) {
                //+指定时间内的点击量及曝光量
                $recommend_where = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where[] = ['type', '=', 1];
                $recommend_where[] = ['courseid', '=', $v['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $v['exposure'] = 0;
                } else {
                    $v['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $v['click'] = 0;
                } else {
                    $v['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $v['ctr'] = 0;
                } else {
                    $v['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $v['course_type'] = 'Spark';
            }

            //+轻学堂数据
            $qxt_course = array_unique($qxt);
            $qxt_where = [];
            $qxt_where[] = ['id', 'in', $qxt_course];
            if (!empty($get['key'])) {
                $qxt_where[] = ['courseTitle', 'like', '%'.$get['key'].'%'];
            }
            $qxt_data = Db::name('qxt_courses')
                ->where($qxt_where)
                ->field('id, courseTitle as title')
                ->select()->toArray();

            foreach ($qxt_data as &$value) {
                //+指定时间内的点击量及曝光量
                $recommend_where2 = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where2[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where2[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where2[] = ['type', '=', 3];
                $recommend_where2[] = ['courseid', '=', $value['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where2)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $value['exposure'] = 0;
                } else {
                    $value['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $value['click'] = 0;
                } else {
                    $value['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $value['ctr'] = 0;
                } else {
                    $value['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $value['course_type'] = '轻学堂';
            }


            $data = array_merge($spark_data, $qxt_data);

        } else if($get['course_type'] == 1) {
            $spark_course = array_unique($spark);
            $spark_where = [];
            $spark_where[] = ['id', 'in', $spark_course];

            if (!empty($get['key'])) {
                $spark_where[] = ['title', 'like', '%'.$get['key'].'%'];
            }
            $data = Db::name('learning')
                ->where($spark_where)
                ->field('id, title')
                ->select()->toArray();

            foreach ($data as &$v) {
                //+指定时间内的点击量及曝光量
                $recommend_where = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where[] = ['type', '=', 1];
                $recommend_where[] = ['courseid', '=', $v['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $v['exposure'] = 0;
                } else {
                    $v['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $v['click'] = 0;
                } else {
                    $v['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $v['ctr'] = 0;
                } else {
                    $v['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $v['course_type'] = 'Spark';
            }

        } else if($get['course_type'] == 3) {
            $qxt_course = array_unique($qxt);
            $qxt_where = [];
            $qxt_where[] = ['id', 'in', $qxt_course];
            if (!empty($get['key'])) {
                $qxt_where[] = ['courseTitle', 'like', '%'.$get['key'].'%'];
            }
            $data = Db::name('qxt_courses')
                ->where($qxt_where)
                ->field('id, courseTitle as title')
                ->select()->toArray();

            foreach ($data as &$value) {
                //+指定时间内的点击量及曝光量
                $recommend_where2 = [];
                if (!empty($get['time1'])) {
                    $t = explode(' - ', $get['time1']);
                    $recommend_where2[] = ['date', 'between', [date('Ymd',strtotime($t[0])), date('Ymd',strtotime($t[1]))]];
                } else {
                    $start = 20200101;
                    $end = date('Ymd', time());
                    $recommend_where2[] = ['date', 'between', [$start, $end]];
                }
                $recommend_where2[] = ['type', '=', 3];
                $recommend_where2[] = ['courseid', '=', $value['id']];
                $recommend = Db::name('on_line_recommend_data')->where($recommend_where2)->field('SUM(exposure) as exposure, SUM(click) as click')->find();
                if (empty($recommend['exposure'])) {
                    $value['exposure'] = 0;
                } else {
                    $value['exposure'] = $recommend['exposure'];
                }
                if (empty($recommend['click'])) {
                    $value['click'] = 0;
                } else {
                    $value['click'] = $recommend['click'];
                }

                if ($recommend['exposure'] == 0) {
                    $value['ctr'] = 0;
                } else {
                    $value['ctr'] = number_format(($recommend['click']/$recommend['exposure']), 2);
                }
                $value['course_type'] = '轻学堂';
            }

        }

        if ( !empty($get['field']) && !empty($get['order'])) {
            $field = $get['field'];
            $sort = $get['order'];
            $data = sort_array($data, $field, $sort);
        }

        foreach ($data as $value) {
            $arr = [
                $value['id'],
                $value['title'],
                $value['course_type'],
                $value['ctr'],
                $value['exposure'],
                $value['click'],
            ];
            fputcsv($fp, $arr);
        }
        //每1千条数据就刷新缓冲区
        ob_flush();
        flush();
    }
}